*****************************************************
*****************Initialize dofile*******************
*****************************************************

clear all
clear matrix
set more off, perm
cd "E:/ProjData/Workplace, elite/"
capture log close
log using "C:/Userdata/Shared/Logs/Workplace, elite/initialize.text", replace text									
do "C:/Userdata/Shared/Dofiles/DoData/Workplace, elite/programs.do"				/*See separate program dofile*/


*******************************************************************************************
* This dofiles concists of the following parts:
* (1): Cleaning the data registers that we use in the project (dropping duplicates etc)
* (2): Saving relevant variables to reduce the size of the data sets 
********************************************************************************************

* Elected politicians*

foreach j in KO LT RI {											
	foreach k in 1982 1985 1988 {
	use "D:/SCB_ConPol/Stata/NomVald/valda_`j'_82_85_88.dta", clear
		keep if valar == `k'
		capture gen nrinom = .
		capture destring nrinom, replace force
		capture rename Partikod partikod
		gen politiker_`j' = 1
		
		gen partyabb = ""
		if "`j'" == "KO" {
			replace partyabb = "c" if pnamn_kort == "Centerpartiet"
			replace partyabb = "l" if pnamn_kort == "Folkpartiet"
			replace partyabb = "kd" if pnamn_kort == "Kristdemokraterna"
			replace partyabb = "m" if pnamn_kort == "Miljöpartiet"
			replace partyabb = "mp" if pnamn_kort == "Moderaterna"
			replace partyabb = "s" if pnamn_kort == "Socialdemokraterna"
			replace partyabb = "v" if pnamn_kort == "Vänsterpartiet"
			replace partyabb = "sd" if pnamn_kort == "Sverigedemokraterna" // Finns ej
		}
		
		if "`j'" != "KO" {
			replace partyabb = "c" if partikod == 4
			replace partyabb = "l" if partikod == 3
			replace partyabb = "kd" if partikod == 68
			replace partyabb = "m" if partikod == 1
			replace partyabb = "mp" if partikod == 55
			replace partyabb = "s" if partikod == 2
			replace partyabb = "v" if partikod == 5
			replace partyabb = "sd" if partikod == 110
		}
		rename partyabb partyabbtemp
		bysort LopNr: egen partyabb = mode(partyabbtemp)
		collapse politiker_`j' (min) nrinom (firstnm) partyabb, by(LopNr) 										/*We are interested in having a politicians as a class, not which constituency a politician was elected in. Therefore we may collapse*/
		gen year = `k'
		label var politiker_`j' ""
		save "Valda_`k'_`j'.dta", replace
		}
	}
foreach j in KO LT RI {
	foreach k in 1991 1994 1998 2002 2006 2010 2014 {
		use "D:/SCB_ConPol/Stata/NomVald/Valda_`k'_`j'.dta", clear
		capture gen nrinom = .
		capture destring nrinom, replace force
		capture rename Partikod partikod
		gen politiker_`j' = 1
		gen partyabb = ""
		replace partyabb = "c" if partikod == 4
		replace partyabb = "l" if partikod == 3
		replace partyabb = "kd" if partikod == 68
		replace partyabb = "m" if partikod == 1
		replace partyabb = "mp" if partikod == 55
		replace partyabb = "s" if partikod == 2
		replace partyabb = "v" if partikod == 5
		replace partyabb = "sd" if partikod == 110
		rename partyabb partyabbtemp
		bysort LopNr: egen partyabb = mode(partyabbtemp), maxmode
		collapse politiker_`j' (min) nrinom partyabb, by(LopNr) 
		gen year = `k'
		label var politiker_`j' ""
		save "Valda_`k'_`j'.dta", replace
		}
	}
	
	
*2018; no separate "elected file" - use nominated file to create for 2018
foreach j in KO LT RI {
		use "D:/SCB_ConPol/Stata/NomVald/Nom_2018_`j'.dta", clear
		drop if vald==0
		drop nrinom
		gen nrinom =.
		destring nrinom, replace force
		gen politiker_`j' = 1
		gen partyabb = ""
		replace partyabb = "c" if partinamn == "Centerpartiet"
		replace partyabb = "l" if partinamn == "Liberalerna (tidigare Folkpartiet)"
		replace partyabb = "kd" if partinamn == "Kristdemokraterna"
		replace partyabb = "m" if partinamn == "Moderaterna"
		replace partyabb = "mp" if partinamn == "Miljöpartiet de gröna"
		replace partyabb = "s" if partinamn == "Arbetarepartiet-Socialdemokraterna"
		replace partyabb = "v" if partinamn == "Vänsterpartiet"
		replace partyabb = "sd" if partinamn == "Sverigedemokraterna"
		rename partyabb partyabbtemp
		bysort LopNr: egen partyabb = mode(partyabbtemp), maxmode
		collapse politiker_`j' (min) nrinom partyabb, by(LopNr) 
		gen year = 2018
		label var politiker_`j' ""
		save "Valda_2018_`j'.dta", replace
		}
	

* Nominated politicians*
foreach k in 1982 1985 1988 {
	use "D:/SCB_ConPol/Stata/NomVald/Nominerade_KO_82_85_88_94.dta", clear
	keep if valar == `k'
	capture gen nrinom = .
	capture destring nrinom, replace force
	capture rename Partikod partikod
	gen politiker_`j' = 1
	*generate list id; that is eqivalent to the list where the ind. has his/her lowest list number
	sort LopNr nrinom
	bysort LopNr : gen listmin = _n
	gen listidtemp = listnr if listmin==1
	bysort LopNr: egen listid = max(listidtemp)
	*now create percentile rank
	bysort listnr: egen maxlist = max(nrinom)
	gen maxlisttemp = maxlist if listmin==1
bysort LopNr: egen totallist = max(maxlisttemp)
	
	bysort LopNr: egen parti = mode(partikod), maxmode
	collapse politiker_`j' (min) totallist listid nrinom parti, by(LopNr) 		
	gen partyabb = ""
	replace partyabb = "c" if parti == 4
	replace partyabb = "l" if parti == 3
	replace partyabb = "kd" if parti == 68
	replace partyabb = "m" if parti == 1
	replace partyabb = "mp" if parti == 55
	replace partyabb = "s" if parti == 2
	replace partyabb = "v" if parti == 5
	replace partyabb = "sd" if parti == 110
	gen year = `k'
	label var politiker_`j' ""
	save "Nom_`k'_KO.dta", replace
	}

foreach j in LT RI {											
	foreach k in 1982 1985 1988 {
		use "D:/SCB_ConPol/Stata/NomVald/Nominerade_`j'_82_85_88.dta", clear
		keep if valar == `k'
		capture gen nrinom = .
		capture destring nrinom, replace force
		capture rename Partikod partikod
		gen politiker_`j' = 1	
		bysort LopNr: egen parti = mode(partikod), maxmode
		collapse politiker_`j' (min) parti nrinom, by(LopNr) 		
		gen partyabb = ""
		replace partyabb = "c" if parti == 4
		replace partyabb = "l" if parti == 3
		replace partyabb = "kd" if parti == 68
		replace partyabb = "m" if parti == 1
		replace partyabb = "mp" if parti == 55
		replace partyabb = "s" if parti == 2
		replace partyabb = "v" if parti == 5
		replace partyabb = "sd" if parti == 110
		gen year = `k'
		label var politiker_`j' ""
		label var parti ""
		save "Nom_`k'_`j'.dta", replace
		}
	}
foreach j in KO LT RI {
	foreach k in 1991 1994 1998 2002 2006 2010 2014 2018 {
		use "D:/SCB_ConPol/Stata/NomVald/Nom_`k'_`j'.dta", clear
		capture gen nrinom = .
		capture destring nrinom, replace force
			capture gen listnr = .
		capture destring listnr, replace force
		capture rename Partikod partikod
		*for 2018:
				capture destring partikod, replace force

		gen politiker_`j' = 1
		*generate list id; that is eqivalent to the list where the ind. has his/her lowest list number
			sort LopNr nrinom
	bysort LopNr : gen listmin = _n
	gen listidtemp = listnr if listmin==1
	bysort LopNr: egen listid = max(listidtemp)
	
		*now create percentile rank
	bysort listnr: egen maxlist = max(nrinom)
	gen maxlisttemp = maxlist if listmin==1
bysort LopNr: egen totallist = max(maxlisttemp)
	
		bysort LopNr: egen parti = mode(partikod), maxmode
		collapse politiker_`j' (min) totallist listid parti nrinom, by(LopNr) 
		gen partyabb = ""
		replace partyabb = "c" if parti == 4
		replace partyabb = "l" if parti == 3
		replace partyabb = "kd" if parti == 68
		replace partyabb = "m" if parti == 1
		replace partyabb = "mp" if parti == 55
		replace partyabb = "s" if parti == 2
		replace partyabb = "v" if parti == 5
		replace partyabb = "sd" if parti == 110
		gen year = `k'
		label var politiker_`j' ""
		label var parti ""
		save "Nom_`k'_`j'.dta", replace
		}
	}
	


* Multigenerational register*
use "D:/SCB_ConPol/Stata/flergen/Foraldrar_2020.dta", clear
rename LopNrMor lopnrmor
rename LopNrFar lopnrfar
keep LopNr lopnrmor lopnrfar
dupprogram
save "flergen.dta", replace

* Data on birth year/month and immigration status*
use "D:/SCB_ConPol/Stata/RTB/Fodelseuppg_2020.dta", clear
dupprogram

merge 1:1 LopNr using "D:/SCB_ConPol/Stata/RTB/FodelselandIFAUGrupp_2020.dta"
destring Kon, force replace
keep LopNr FodArMan Kon Fodelsevarldsdel
save "Fodelseuppg.dta", replace

*Deso* 
forvalues k = 1982/2018 {
	use "D:/SCB_ConPol/Stata/SAMS/DESO_`k'.dta", clear
	gen year = `k'
	*Create municipality pop
	gen Muni = substr(Deso,1,4)	
	gen pop =1 
	bysort Muni: egen muni_pop = total(pop)
	keep LopNr year Deso Muni muni_pop
	dupprogram
	save "Deso_`k'.dta", replace
}
	

* LISA data for place of work + other covariates		
forvalues k = 2001/2002 {
	use "D:/SCB_ConPol/Stata/LISA/Lisa_`k'.dta", clear
	gen year = `k'
		if year < 2002 {
			rename AstSNI92 Sni
		}
		if year == 2002 {
			rename AstSNI2002 Sni
		}
	
	gen sysselsatt = .
	replace sysselsatt = 1 if SyssStat == 1
	replace sysselsatt = 0 if SyssStat == 5 | SyssStat == 6
	egen zink = std(LoneInk)
	egen zdisp = std(DispInk)
	egen zforled = std(ForLed)
	egen zakassa = std(Akassa)
	YearsEducation

	capture rename SSYK4 ssyk
	
	keep LopNr year AstKommun Yrkstalln AntalSys Sni sysselsatt zink yearseducation ssyk zdisp zforled HsDelt zakassa LoneInk DispInk ForLed Akassa
	dupprogram
	
	merge 1:m LopNr using "D:/SCB_ConPol/Stata/LISA_lopNr/lisa_`k'.dta", keepusing(LopNr_ArbstId)
	drop _merge
	rename LopNr_ArbstId ArbstId
	dupprogram
	save "arbetstalle_`k'.dta", replace
}

forvalues k = 2003/2010 {
	use "D:/SCB_ConPol/Stata/LISA/Lisa_`k'.dta", clear
	gen year = `k'
		if year < 2007 {
			rename AstSNI2002 Sni
		}
		if year >= 2007 {
			rename AstSNI2007 Sni
		}
		if year >= 2005 {					/*Blir detta korrekt?*/
			rename DispInkKE DispInk 
		}
	
	gen sysselsatt = .
	replace sysselsatt = 1 if SyssStatJ == 1
	replace sysselsatt = 0 if SyssStatJ == 5 | SyssStatJ == 6
	egen zink = std(LoneInk)
	egen zdisp = std(DispInk)
	egen zforled = std(ForLed)
	egen zakassa = std(Akassa)
	YearsEducation

	rename SSYK4 ssyk

	keep LopNr year AstKommun Yrkstalln AntalSys Sni sysselsatt zink yearseducation ssyk zdisp zforled HsDelt zakassa LoneInk DispInk ForLed Akassa
	dupprogram
	
	merge 1:m LopNr using "D:/SCB_ConPol/Stata/LISA_lopNr/lisa_`k'.dta", keepusing(LopNr_ArbstId)
	drop _merge
	rename LopNr_ArbstId ArbstId
	dupprogram

	save "arbetstalle_`k'.dta", replace
}

forvalues k = 2011/2014 {
	use "D:/SCB_ConPol/Stata/LISA/Lisa_`k'.dta", clear
	gen year = `k'
		if year < 2007 {
			rename AstSNI2002 Sni
		}
		if year >= 2007 {
			rename AstSNI2007 Sni
		}
		if year >= 2004 {
			rename DispInkKE DispInk 		/*Check this*/
		}
	
	
	gen sysselsatt = .
	replace sysselsatt = 1 if SyssStat11 == 1 | SyssStat11 == 7 
	replace sysselsatt = 0 if SyssStat11 == 5 | SyssStat11 == 6
	
	egen zink = std(LoneInk)
	egen zdisp = std(DispInk)
	egen zforled = std(ForLed)
	egen zakassa = std(Akassa)
	YearsEducation

capture rename SSYK4 ssyk
capture rename ssyk4_2012 ssyk

	keep LopNr year AstKommun Yrkstalln AntalSys Sni sysselsatt zink yearseducation ssyk zdisp zforled HsDelt zakassa LoneInk DispInk ForLed Akassa
	dupprogram
	
	merge 1:m LopNr using "D:/SCB_ConPol/Stata/LISA_lopNr/lisa_`k'.dta", keepusing(LopNr_ArbstId)
	drop _merge
	rename LopNr_ArbstId ArbstId
	dupprogram

	save "arbetstalle_`k'.dta", replace
}


forvalues k = 2015/2017 {
	use "D:/SCB_ConPol/Stata/LISA/Lisa_`k'.dta", clear
	gen year = `k'
	
	rename AstSNI2007 Sni
	rename DispInkKE DispInk 		/*Check this*/	
	
	gen sysselsatt = .
	replace sysselsatt = 1 if SyssStat11 == "1" | SyssStat11 == "7" 
	replace sysselsatt = 0 if SyssStat11 == "5" | SyssStat11 == "6"
	
	egen zink = std(LoneInk)
	egen zdisp = std(DispInk)
	egen zforled = std(ForLed)
	egen zakassa = std(Akassa)
	
	capture rename Sun2000niva_Old Sun2000niva_old
	YearsEducation

capture rename SSYK4 ssyk
capture rename ssyk4_2012 ssyk
capture rename Ssyk4_2012 ssyk
*2017; only KU1!
capture rename KU1Ssyk4_2012 ssyk

capture rename YrkStalln Yrkstalln
destring Yrkstalln, replace force

destring Sni, replace force
rename HSDelt HsDelt
destring HsDelt, replace force

destring AstKommun, force replace

	keep LopNr year AstKommun Yrkstalln AntalSys Sni sysselsatt zink yearseducation ssyk zdisp zforled HsDelt zakassa LoneInk DispInk ForLed Akassa
	dupprogram
	
	merge 1:m LopNr using "D:/SCB_ConPol/Stata/LISA_lopNr/lisa_`k'.dta", keepusing(LopNr_ArbstId)
	drop _merge
	rename LopNr_ArbstId ArbstId
	dupprogram

	save "arbetstalle_`k'.dta", replace
}

*2018 (only the covaraites for this year)*
use "D:/SCB_ConPol/Stata/LISA/Lisa_2018.dta", clear
gen year = 2018
	
rename AstSNI2007 Sni
rename DispInkKE DispInk 		/*Check this*/	
	
gen sysselsatt = .
replace sysselsatt = 1 if SyssStat11 == "1" | SyssStat11 == "7" 
replace sysselsatt = 0 if SyssStat11 == "5" | SyssStat11 == "6"
	
egen zink = std(LoneInk)
egen zdisp = std(DispInk)
egen zforled = std(ForLed)
egen zakassa = std(Akassa)
	
capture rename Sun2000niva_Old Sun2000niva_old
YearsEducation

capture rename SSYK4 ssyk
capture rename ssyk4_2012 ssyk
capture rename Ssyk4_2012 ssyk

capture rename KU1Ssyk4_2012 ssyk

capture rename YrkStalln Yrkstalln
destring Yrkstalln, replace force

destring Sni, replace force
rename HSDelt HsDelt
destring HsDelt, replace force

destring AstKommun, force replace
dupprogram

keep LopNr year AstKommun Yrkstalln AntalSys Sni sysselsatt zink yearseducation ssyk zdisp zforled HsDelt zakassa LoneInk DispInk ForLed Akassa
save "covs_2018.dta", replace




* Panel place of work *
use "arbetstalle_2001", clear
forvalues k = 2002(1)2017 {														// We do not need information on place of work for 2018, but only in previous mandate period
	append using "arbetstalle_`k'" 
}
drop if year==. 																/*Some minor irregularities across lisa files; minor numnber - 350 obs out of 130 millions obs*/
compress
save "arbetstalle_panel.dta", replace

* Panel place of residence deso *

use "Deso_2001", clear
forvalues k = 2002(1)2018 {
	append using "Deso_`k'" 
}
compress
gen KKod = substr(Deso,1,4)
save "Deso_panel.dta", replace



**Election results kommunfullmäktige**
import excel "D:/Data/ExtData/ValResData/Kommunalvalen1973-2018/MandatKommun.xlsx", sheet("use mandat") firstrow clear
carryforward Kod Kommun, replace

reshape long E, i(Kod Parti) j(year)
rename E Mandat

gen partival = .
replace partival = 1 if Parti == "Moderaterna"
replace partival = 2 if Parti == "Kristdemokraterna"
replace partival = 3 if Parti == "Liberalerna"
replace partival = 4 if Parti == "Centerpartiet"
replace partival = 5 if Parti == "Miljöpartiet"
replace partival = 6 if Parti == "Socialdemokraterna"
replace partival = 7 if Parti == "Vänsterpartiet"
replace partival = 8 if Parti == "Sverigedemokraterna"
replace partival = 9 if Parti == "övriga partier"


drop Parti
reshape wide Mandat, i(Kod year) j(partival) 
rename Mandat1 MandatM
rename Mandat2 MandatKD
rename Mandat3 MandatL
rename Mandat4 MandatC
rename Mandat5 MandatMP
rename Mandat6 MandatS
rename Mandat7 MandatV
rename Mandat8 MandatSD
rename Mandat9 MandatÖ

order Kod Kommun year
leftalign Kod Kommun
destring MandatM MandatKD MandatL MandatC MandatMP MandatS MandatV MandatSD MandatÖ year, replace
foreach k in MandatM MandatKD MandatL MandatC MandatMP MandatS MandatV MandatSD MandatÖ {
	label var `k' ""
	replace `k' = 0 if `k' == .
}
gen totalmandat = MandatM + MandatKD + MandatL + MandatC + MandatMP + MandatS + MandatV + MandatSD + MandatÖ

keep if year > 1994
replace year = 2001 if year == 1998
save "MandatMuni.dta", replace



**Election results landsting**
import excel "D:/Data/ExtData/ValResData/Landstingsvalen1973_2018/MandatLandsting.xlsx", sheet("use mandat") firstrow clear
carryforward Kod Landsting, replace

replace Kod = "12A" if Kod == "12" & Landsting == "Skåne läns landsting"		/*Samma landstingskod. Ändrar tillfälligt fört att kunna köra reshape*/
replace Kod = "12B" if Kod == "12" & Landsting == "Malmöhus läns landsting"
replace Kod = "14A" if Kod == "14" & Landsting == "Västra Götalands läns landsting"
replace Kod = "14B" if Kod == "14" & Landsting == "Bohuslandstinget"



reshape long E, i(Kod Parti) j(year)
rename E MandatL

gen partival = .
replace partival = 1 if Parti == "Moderaterna"
replace partival = 2 if Parti == "Kristdemokraterna"
replace partival = 3 if Parti == "Liberalerna"
replace partival = 4 if Parti == "Centerpartiet"
replace partival = 5 if Parti == "Miljöpartiet"
replace partival = 6 if Parti == "Socialdemokraterna"
replace partival = 7 if Parti == "Vänsterpartiet"
replace partival = 8 if Parti == "Sverigedemokraterna"
replace partival = 9 if Parti == "övriga partier"


drop Parti
reshape wide Mandat, i(Kod year) j(partival) 
rename MandatL1 MandatLM
rename MandatL2 MandatLKD
rename MandatL3 MandatLL
rename MandatL4 MandatLC
rename MandatL5 MandatLMP
rename MandatL6 MandatLS
rename MandatL7 MandatLV
rename MandatL8 MandatLSD
rename MandatL9 MandatLÖ

order Kod Landsting year
leftalign Kod Landsting
destring MandatLM MandatLKD MandatLL MandatLC MandatLMP MandatLS MandatLV MandatLSD MandatLÖ year, replace
foreach k in MandatLM MandatLKD MandatLL MandatLC MandatLMP MandatLS MandatLV MandatLSD MandatLÖ {
	label var `k' ""
	replace `k' = 0 if `k' == .
}
gen totalmandatL = MandatLM + MandatLKD + MandatLL + MandatLC + MandatLMP + MandatLS + MandatLV + MandatLSD + MandatLÖ

drop if Landsting == "Malmöhus läns landsting" & year > 1994 & year != .		/*Malmöhus läns landsting existerade fram till 1998.*/		
drop if Landsting == "Skåne läns landsting" & year <=1994						/*...Efter 1998 finns Skåne läns landsting (Region Skåne)*/
replace Kod = "12" if Kod == "12A" 
replace Kod = "12" if Kod == "12B" 
drop if Landsting == "Kristianstads läns landsting" & year > 1994 & year != .	/*Slutade existera 1998. Uppgick i Skåne läns landsting*/

drop if Landsting == "Bohuslandstinget" & year > 1994 & year != .				/*Bohuslandstinget existerar fram till 1998*/
drop if Landsting == "Västra Götalands läns landsting" & year <=1994			/*...Efter 1998 finns Västra Götelands läns landsting (Region Västra Göteland)*/
replace Kod = "14" if Kod == "14A" 
replace Kod = "14" if Kod == "14B" 
drop if Landsting == "Älvsborgs läns landsting" & year > 1994 & year != .		/*Slutade existera 1998. Uppgick i Västra Götelands landsting*/
drop if Landsting == "Skaraborgs läns landsting" & year > 1994 & year != .		/*Slutade existera 1998. Uppgick i Västra Götelands landsting*/

rename Kod LKod

keep if year > 1994
replace year = 2001 if year == 1998

save "MandatLandsting.dta", replace												/*Slutresultatet är en obalanserad panel där enbart landsting som existerar ett givet år finns med*/


**Election results riksdagen**
import excel "D:/Data/ExtData/ValResData/Riksdagsvalen1973_2018/MandatRiksdag.xlsx", sheet("mandat use") firstrow allstring clear

reshape long E, i(Parti) j(year)
rename E MandatR

gen partival = .
replace partival = 1 if Parti == "Moderaterna"
replace partival = 2 if Parti == "Kristdemokraterna"
replace partival = 3 if Parti == "Liberalerna"
replace partival = 4 if Parti == "Centerpartiet"
replace partival = 5 if Parti == "Miljöpartiet"
replace partival = 6 if Parti == "Socialdemokraterna"
replace partival = 7 if Parti == "Vänsterpartiet"
replace partival = 8 if Parti == "Sverigedemokraterna"
replace partival = 10 if Parti == "Ny demokrati"


drop Parti
reshape wide Mandat, i(year) j(partival) 
rename MandatR1 MandatRM
rename MandatR2 MandatRKD
rename MandatR3 MandatRL
rename MandatR4 MandatRC
rename MandatR5 MandatRMP
rename MandatR6 MandatRS
rename MandatR7 MandatRV
rename MandatR8 MandatRSD
rename MandatR10 MandatRND

order year
destring MandatRM MandatRKD MandatRL MandatRC MandatRMP MandatRS MandatRV MandatRSD MandatRND year, replace
foreach k in MandatRM MandatRKD MandatRL MandatRC MandatRMP MandatRS MandatRV MandatRSD MandatRND {
	label var `k' ""
	replace `k' = 0 if `k' == .
}
gen totalmandat = MandatRM + MandatRKD + MandatRL + MandatRC + MandatRMP + MandatRS + MandatRV + MandatRSD + MandatRND

keep if year > 1994
replace year = 2001 if year == 1998


save "MandatRiksdagen.dta", replace


**Grades from ninth grade **
use "D:/SCB_ConPol/Stata/Utbild/AK9.dta", clear 
keep LopNr MEDELBETYG MERITVARDE SH
foreach v in MEDELBETYG SH MERITVARDE {
      rename `v' `= lower("`v'")'
}
dupprogram
gen betygsystem = .
replace betygsystem = 0 if medelbetyg !=9
replace betygsystem = 1 if medelbetyg == 9

replace medelbetyg = . if medelbetyg == 9		
replace meritvarde = . if medelbetyg !=.
gen GPA_9 = medelbetyg
replace GPA_9 = meritvarde if medelbetyg == .
drop medelbetyg meritvarde
save "AK9.dta", replace

* Conscription data *
use "D:/SCB_ConPol/Stata/monstring/da856.dta", clear
split Monstringsdatum, parse(/)
rename Monstringsdatum3 Conscriptionyear
keep LopNr Befl Psyf Gkap Gvkap Gckap Conscriptionyear
destring Conscriptionyear, replace
save "conscription.dta", replace

use "D:/SCB_ConPol/Stata/monstring/insark.dta", clear
gen Conscriptionyear = substr(INST,1,2)
replace Conscriptionyear = "19"+Conscriptionyear 
replace Conscriptionyear = trim(Conscriptionyear)
replace Conscriptionyear = "" if strlen(Conscriptionyear) < 4

rename PPRF_PGRP Gkap
rename PPRF_PF Psyf
rename PPRF_BEFL Befl
destring Gkap Psyf Befl Conscriptionyear, replace force
keep LopNr Gkap Psyf Befl Conscriptionyear
duplicates tag LopNr, gen(d)		
bysort LopNr (Conscriptionyear): keep if _n == 1
drop d
save "conscription2.dta", replace



* Grades from upper secondary school *
use "D:/SCB_ConPol/Stata/Utbild/AvgGymn.dta", clear
keep LopNr avgar mbet GBP Skolkod_LopNr
gen betygsystem = .
replace betygsystem = 1 if mbet != . & GBP == .
replace betygsystem = 2 if GBP != . & mbet == .
duplicates tag LopNr, gen(d)

bysort LopNr (avgar): keep if _n == _N		//Sparar sista betyget
drop d

save "betyg.dta", replace




** Tillägg Olles kod marginal seats **
import excel "D:/Data/ExtData/ValResData/Kommunalvalen1973-2018/MandatKommun.xlsx", sheet("use mandat") firstrow clear
carryforward Kod Kommun, replace

reshape long E, i(Kod Parti) j(year)
rename E Mandat


keep if year == 2018

gen parti_initial = ""
replace parti_initial = "M" if Parti == "Moderaterna"
replace parti_initial = "K" if Parti == "Kristdemokraterna"
replace parti_initial = "F" if Parti == "Liberalerna"
replace parti_initial = "C" if Parti == "Centerpartiet"
replace parti_initial = "G" if Parti == "Miljöpartiet"
replace parti_initial = "S" if Parti == "Socialdemokraterna"
replace parti_initial = "V" if Parti == "Vänsterpartiet"
replace parti_initial = "B" if Parti == "Sverigedemokraterna"
replace parti_initial = "N" if Parti == "övriga partier"

rename Kod m_id
destring m_id, replace
rename Mandat seats
drop Parti Kommun
destring seats, replace
bysort m_id year : egen tot_seats = sum(seats)
gen seat_share = seats/tot_seats

save "addOlleskod2018temp1.dta", replace


import excel "D:/Data/ExtData/ValResData/Kommunalvalen1973-2018/ValresultatKommun.xlsx", sheet("use andel") firstrow clear
carryforward Kod Kommun, replace

reshape long E, i(Kod Parti) j(year)
rename E seatshare


keep if year == 2018

gen parti_initial = ""
replace parti_initial = "M" if Parti == "Moderaterna"
replace parti_initial = "K" if Parti == "Kristdemokraterna"
replace parti_initial = "F" if Parti == "Liberalerna"
replace parti_initial = "C" if Parti == "Centerpartiet"
replace parti_initial = "G" if Parti == "Miljöpartiet"
replace parti_initial = "S" if Parti == "Socialdemokraterna"
replace parti_initial = "V" if Parti == "Vänsterpartiet"
replace parti_initial = "B" if Parti == "Sverigedemokraterna"
replace parti_initial = "N" if Parti == "övriga partier"

rename Kod m_id
destring m_id, replace
drop Parti Kommun
destring seatshare, replace

drop if parti_initial == ""
save "addOlleskod2018temp2.dta", replace

* Add together*
use "addOlleskod2018temp2.dta", clear
merge 1:1 m_id parti_initial using "addOlleskod2018temp1.dta"
drop _merge
gen elec = seats
replace elec = 0 if elec == .
bysort m_id year : egen elec_tot = sum(elec)

save "addOlleskod2018.dta", replace

use "D:/Data/ExtData/contestability/base data contestability.dta", clear

append using "addOlleskod2018.dta"
drop _merge valar


*calculate relevant lags
sort parti_initial m_id year
foreach var of varlist seats seat_share vote_share tot_seats{
	gen `var'_lag= `var'[_n-1] if m_id== m_id[_n-1] & parti==parti[_n-1]
	gen `var'_2lag= `var'[_n-2] if m_id== m_id[_n-2] & parti==parti[_n-2]
	gen `var'_3lag= `var'[_n-3] if m_id== m_id[_n-3] & parti==parti[_n-3]
	gen `var'_3avg =(`var'_lag+`var'_2lag +`var'_3lag)/3
	gen `var'_23lag =(`var'_2lag +`var'_3lag)/2
	gen `var'_3diff =`var'_lag-`var'_3avg
}

*gen council_ch=tot_seats-tot_seats_lag
*gen sslag_cch=council_ch*seat_share_lag

set matsize 10000

 forvalues n=1/75{
	gen sd_`n' = seats>=`n' & seats!=.
 }
 forvalues n=1/75{
	gen sd_`n'_lag = seats_lag>=`n' & seats_lag!=.
 }
 forvalues n=1/75{
	gen sd_`n'_2lag = seats_2lag>=`n' & seats_2lag!=.
 }
 
forvalues n=1/75{
	gen  ss_sd_`n'_lag= seat_share_lag*sd_`n'_lag
}
 
forvalues n=1/75{
	gen  pch_sd_`n'_lag= poll_relch*sd_`n'_lag
}
 
forvalues n=1/75{
	gen  pch_size_sd_`n'_lag= poll_relch*sd_`n'_lag*tot_seats_lag
}
forvalues n=1/75{
	gen  size_sd_`n'_lag= tot_seats_lag*sd_`n'_lag
}
forvalues n=1/75{
	gen  size_sd_`n'_2lag= tot_seats_2lag*sd_`n'_2lag
}
forvalues n=1/75{
	gen  ss_sd_`n'_23lag= seat_share_23lag*sd_`n'_lag
}
 
 
gen ss_seats=seats*seat_share
gen ss_seats_lag=seats_lag*seat_share_lag
gen pch_seats_lag=seats_lag*  poll_relch
 
*Run prediction regressions
forvalues n=1/75{
reg sd_`n' sd_1_lag- sd_75_lag size_sd_1_lag- size_sd_75_lag  sd_1_2lag- sd_75_2lag size_sd_1_2lag- size_sd_75_2lag 
predict psd_`n'
}

  forvalues n=1/75{
reg sd_`n' sd_1_lag- sd_75_lag  pch_sd_1_lag- pch_sd_75_lag  pch_size_sd_1_lag- pch_size_sd_75_lag size_sd_1_lag- size_sd_75_lag  sd_1_2lag- sd_75_2lag size_sd_1_2lag- size_sd_75_2lag 
predict psd_poll_`n'
}
  forvalues n=1/75{
reg sd_`n' sd_1_lag- sd_75_lag size_sd_1_lag- size_sd_75_lag   
predict psd_1lag_`n'
}

  forvalues n=1/75{
reg sd_`n' sd_1_lag- sd_75_lag  sd_1_2lag- sd_75_2lag 
predict psd_seats_`n'
}



keep parti year m_id  seats seat_share psd_* sd_*

*reshape file so we get one observation per list rank, party, municipality and election period

reshape long sd_@ psd_@ psd_1lag_@ psd_poll_@ psd_seats_@, i(parti year m_id seats seat_share) j(rank_ind)

rename m_id Kod
tostring Kod, replace
replace Kod = "0" + Kod if strlen(Kod) == 3

rename year firstyearnom
drop if firstyearnom < 1998
replace firstyearnom = 2001 if firstyearnom == 1998

rename rank_ind list_KO 

gen parti = .
replace parti = 4 if parti_initial == "C"
replace parti = 3 if parti_initial == "F"
replace parti = 68 if parti_initial == "K"
replace parti = 1 if parti_initial == "M"
replace parti = 55 if parti_initial == "G"
replace parti = 2 if parti_initial == "S"
replace parti = 5 if parti_initial == "V"
replace parti = 110 if parti_initial == "B"



save "contestability.dta", replace

* Population municipality *
import excel "D:\Data\ExtData\Kommun pop\kommunpop.xlsx", sheet("use") firstrow clear
save "popkommun", replace

import excel "D:\Data\ExtData\Kommunkoder\kommunkod.xlsx", sheet("use") firstrow clear
save "kommunkod", replace

use "popkommun", clear
merge 1:1 Kommun using "kommunkod.dta"
drop if _merge == 2 						/*Länskoder*/
drop _merge


reshape long y, i(kod) 
rename _j year
rename y pop
rename kod Kod


save "kommunpoplong", replace

use "MandatMuni",clear			/*Borde vi även ta hänsyn till landstings och riksdagsmandat?*/

merge 1:1 Kod year using "kommunpoplong.dta"
drop if _merge == 2
drop _merge 

keep Kod Kommun year totalmandat pop
gen mandatpop = totalmandat / pop
replace mandatpop = . if totalmandat == 0 
replace mandatpop = . if pop == 0 

save "mandatpop", replace




****************
clear all
log close




**************




